<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Clusters extends CI_Model {

	function __construct() {
        parent::__construct();
    }

    public function exists($locality, $cluster) {
    	$id = self::getId($locality, $cluster);
    	return ($id > 0);
    }

    public function getAll() {
    	$sql = "SELECT " .
			   "c.ClusterId AS Id, " .
			   "c.LocalityId, " .
			   "l.Locality, " .
			   "c.Cluster, " .
			   "c.Description " .
			   "FROM " .
			   "clusters AS c " .
			   "INNER JOIN localities AS l ON c.LocalityId = l.LocalityId " .
			   "INNER JOIN regions AS r ON l.RegionId = r.RegionId " .
			   "INNER JOIN regiongroups AS rg ON r.RegionGroupId = rg.RegionGroupId " .
			   "WHERE " .
			   "(rg.CountryId = " . COUNTRY_ID . ") " .
			   "ORDER BY " .
			   "l.Locality, " .
			   "c.Cluster";

		$query = $this->db->query($sql);
		return $query->result_array();
    }

    public function getId($locality, $cluster) {
    	$id = 0;

    	$sql = "SELECT " .
    		   "c.`ClusterId` AS `Id` " .
    		   "FROM " .
    		   "`clusters` AS c " .
    		   "WHERE " . 
    		   "(c.LocalityId = " . $locality . ") AND " .
    		   "(c.Cluster LIKE " . $this->db->escape((string) $cluster) . ")";

    	$query = $this->db->query($sql);
    	if ($query->num_rows() > 0) {
    		$row = $query->row_array();
    		$id = $row['Id'];
    	}

    	return $id;
    }

    public function save($updates) {
    	$saved = true;

    	$this->db->trans_begin();

    	foreach ($updates as $update) {
    		$sql = '';
    		$params = array();

    		if ($update->RowState == 'added') {
    			$sql = "INSERT INTO `clusters` " .
    				   "(`LocalityId`, `Cluster`, `Description`, `DateCreated`) " .
    				   "VALUES " . 
    				   "(?, ?, ?, NOW())";

    			$params = array( $update->Locality, $update->Cluster, $update->Description );
    		}
    		else if ($update->RowState == 'modified') {
    			$sql = "UPDATE `clusters` SET " .
    				   "`LocalityId` = ?, `Cluster` = ?, `Description` = ? " .
    				   "WHERE " . 
    				   "(`ClusterId` = ?)";

    			$params = array( $update->Locality, $update->Cluster, $update->Description, $update->Id );
    		}
    		else if ($update->RowState == 'deleted') {
    			$sql = "DELETE FROM `clusters` WHERE (`ClusterId` = ?)";

    			$params = array( $update->Id );
    		}
    		else {

    		}

    		if (trim($sql) != '') $this->db->query($sql, $params); 
    	}

    	if ($this->db->trans_status() === FALSE) {
            $this->db->trans_rollback();
            $saved = false;
        }
        else {
            $this->db->trans_commit();
        }

    	return $saved;
    }

}